/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */

package jdh;

import java.sql.*;
import javax.swing.AbstractListModel;

/**
 *
 * @author tale
 */
public class LineModel extends AbstractListModel{

    static Backend backend = null;
    Statement statement, ust = null;
    ResultSet rs;
    ResultSetMetaData md;
    String selectTemplate = "";
    String select = "";
    int row = 0;
    boolean hasData = false;

    public LineModel(){
        this("", "");
    }

    public LineModel(String select){
        this(select, "");
    }

    public LineModel(String select, String template){
        this.select = select;
        this.selectTemplate = template;
        if (backend.getConnection() == null)
            System.out.println("Please connect the LineModel.backend.");
        try{
            statement = backend.getConnection().createStatement(ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_READ_ONLY);
        }catch(Exception e){
            System.out.println("LineModel:" + e.toString());
        }
        refresh();
    }

    public void setSelect(String select){
        this.select = select;
        refresh();
    }

    public void setSelectID(String id){
        select = String.format(selectTemplate, id);
        refresh();
    }

    public static void setBackend(Backend server){
        backend = server;
    }

    public boolean refresh(){
        hasData = false;

        if (select.compareTo("") == 0) return true;
        System.out.println("DataModel:" + select);

        try {
            rs = statement.executeQuery(select);
            md = rs.getMetaData();
            if (rs.last()){
                row = rs.getRow();
                rs.beforeFirst();
            }
            hasData = true;
        }catch(SQLException ex) {
            System.out.println(ex.toString());
        }catch(NullPointerException ex){
            System.out.println(ex.toString());
        }finally{
            System.out.println("Row = " + row);
        }
        return hasData;
    }

    public int getSize() {
        return row;
    }

    public Object getElementAt(int index) {
        try{
            rs.beforeFirst();
            while (rs.next()){
                if (rs.getRow() == index + 1)
                        return rs.getString(1);

            }
        }catch(SQLException ex){
            System.out.println(ex.toString());
        }
        return null;
    }
    
    public static LineModel getStudentInModel(){
        String studentInTemplate =
            "SELECT STUDENTS.NAME " +
            " FROM LEARNINGS, STUDENTS " +
            " WHERE LEARNINGS.STUDENTID = STUDENTS.ID " +
            " AND LEARNINGS.PLANID = '%s' ";
        return new LineModel("", studentInTemplate);
    }

    public static LineModel getStudentOutModel(){
        String studentOutTemplate =
            "SELECT NAME FROM STUDENTS WHERE NOT EXISTS(" +
            " SELECT * FROM LEARNINGS" +
            " WHERE LEARNINGS.STUDENTID = STUDENTS.ID" +
            " AND LEARNINGS.PLANID = '%s')";
        return new LineModel("", studentOutTemplate);
    }
}
